USE [wdms]
GO

-- =============================================
-- Author     :		Lohith Ram D V
-- Create date:         07/mar/2011
-- Description:	Add new document.
-- =============================================

/*used @par_doc varbinary(2000) instead of @par_doc varbinary(max)*/

create procedure sp_AddNewDocument(
    @par_userid int,
	@par_title varchar(50),
	@par_doc_type nchar(10),
	@par_doc varbinary(2000))
AS
BEGIN
	DECLARE @lastid int

    IF NOT EXISTS(SELECT [Document].[doc_title] FROM [Document] WHERE [Document].[doc_title]=@par_title AND [Document].[user1_id]=@par_userid)
    BEGIN

		INSERT INTO [Document]([Document].[doc_title], [Document].[doc_create_time], [Document].[doc_last_access], [Document].[doc_type], [Document].[doc], [Document].[user1_id])
		VALUES (@par_title, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, @par_doc_type, @par_doc, @par_userid)
		
		--Gets last inserted id
		SET @lastid = SCOPE_IDENTITY();
		
		INSERT INTO [Doc_Modification]([Doc_Modification].[doc_id], [Doc_Modification].[mod_datetime])
		VALUES (@lastid, CURRENT_TIMESTAMP);
		
		DECLARE @deptid int
		
		SELECT @deptid = (SELECT [User1_Dept].[dept_id]
		FROM [User1_Dept]
		WHERE [User1_Dept].[user1_id]=@par_userid);
		
		INSERT INTO [Doc_Dept]([Doc_Dept].[doc_id], [Doc_Dept].[dept_id])
		VALUES (@lastid, @deptid);
		RETURN 1;
	END
	RETURN 0;
END
